﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using Wedn.Core.Entity;
using Wedn.Interface.DAL;
using Wedn.Utility;

namespace Wedn.DAL.MSSQL
{
    /// <summary>
    /// 用户表操作类(初步审核通过)
    /// </summary>
    public class UserDAO : IUser
    {
        /// <summary>
        /// 像用户信息表插入一行数据, 返回执行结果受影响行数
        /// </summary>
        /// <param name="user">要添加的用户对象</param>
        /// <returns>执行结果受影响行数</returns>
        public int Insert(User user)
        {
            string sql = @"INSERT INTO [&#WednTablePrefix%&_Users]
                                    (
                                        user_login,
                                        user_nickname,
                                        user_password,
                                        user_email,
                                        user_url,
                                        user_regdate,
                                        user_status,
                                        user_role
                                    )
                                    VALUES
                                    (
                                        @Login,
                                        @NickName,
                                        @Password,
                                        @Email,
                                        @Url,
                                        @RegDate,
                                        @Status,
                                        @Role
                                    );";
            return ExecuteSql(user, sql);
        }

        /// <summary>
        /// 根据用户ID删除用户表中的用户记录, 返回执行结果受影响行数
        /// </summary>
        /// <param name="id">要删除的用户ID</param>
        /// <returns>执行结果受影响行数</returns>
        public int Delete(int id)
        {
            string sql = "DELETE FROM [&#WednTablePrefix%&_Users] WHERE [user_id] = @Id;";
            return SqlHelper.ExecuteNonQuery(sql, new SqlParameter("@Id", id));
        }

        /// <summary>
        /// 根据用户ID数组删除用户表中的用户记录, 返回执行结果受影响行数
        /// </summary>
        /// <param name="ids">要删除的用户ID数组</param>
        /// <returns>执行结果受影响行数</returns>
        public int Delete(int[] ids)
        {
            //拼接要删除的ID
            string ps = string.Join(",", ids);
            //还是有问题的  问题不大  暂且这么用吧, 版本更新在换参数化
            string sql = "DELETE FROM [&#WednTablePrefix%&_Users] WHERE [user_id] IN (" + ps + ");";
            return SqlHelper.ExecuteNonQuery(sql);
        }

        /// <summary>
        /// 更新一个完整用户对象, 返回执行结果受影响行数
        /// </summary>
        /// <param name="user">更新的用户对象</param>
        /// <returns>执行结果受影响行数</returns>
        public int UpdateUser(User user)
        {
            string sql = @"UPDATE [&#WednTablePrefix%&_Users]
                                    SET
                                        [user_login]=@Login,
                                        [user_nickname]=@NickName,
                                        [user_password]=@Password,
                                        [user_email]=@Email,
                                        [user_url]=@Url,
                                        [user_regdate]=@RegDate,
                                        [user_status]=@Status,
                                        [user_role]=@Role
                                    WHERE 
                                        [user_id]=@Id;";
            return ExecuteSql(user, sql);
        }

        /// <summary>
        /// 更新用户的个人信息, 返回执行结果受影响行数
        /// </summary>
        /// <param name="id">用户ID</param>
        /// <param name="nickname">更新的昵称(不更新则传入null)</param>
        /// <param name="email">更新的Email(不更新则传入null)</param>
        /// <param name="url">更新的网址(不更新则传入null)</param>
        /// <returns>执行结果受影响行数</returns>
        public int UpdateInfo(int id, string nickname, string email, string url)
        {
            List<string> strs = new List<string>();
            List<SqlParameter> ps = new List<SqlParameter>();
            bool flag = false;
            if (!string.IsNullOrEmpty(nickname))
            {
                flag = true;
                strs.Add("[user_nickname]=@NickName");
                ps.Add(new SqlParameter("@NickName", nickname));
            }
            if (!string.IsNullOrEmpty(email))
            {
                flag = true;
                strs.Add("[user_email]=@Email");
                ps.Add(new SqlParameter("@Email", email));
            }
            if (!string.IsNullOrEmpty(url))
            {
                flag = true;
                strs.Add("[user_url]=@Url");
                ps.Add(new SqlParameter("@Url", url));
            }
            if (flag)
            {
                //SQL语句有效
                string sql = @"UPDATE [&#WednTablePrefix%&_Users]
                                        SET
                                            {0}
                                        WHERE 
                                            [user_id]=@Id;";
                sql = string.Format(sql, string.Join(",", strs));
                ps.Add(new SqlParameter("@Id", id));
                return SqlHelper.ExecuteNonQuery(sql, ps.ToArray());
            }
            else
            {
                return 0;
            }
        }

        /// <summary>
        /// 更新用户密码, 返回执行结果受影响行数
        /// </summary>
        /// <param name="id">用户ID</param>
        /// <param name="password">更新的密码</param>
        /// <returns>执行结果受影响行数</returns>
        public int UpdatePassword(int id, string password)
        {
            string sql = @"UPDATE [&#WednTablePrefix%&_Users]
                                    SET
                                        [user_password]=@Password
                                    WHERE 
                                        [user_id]=@Id;";
            return SqlHelper.ExecuteNonQuery(sql,
                new SqlParameter("@Password", password),
                new SqlParameter("@Id", id));
        }

        /// <summary>
        /// 批量更新用户状态, 返回执行结果受影响行数
        /// </summary>
        /// <param name="ids">用户ID</param>
        /// <param name="status">更新的权限(0:正常, 1:待验证审核)</param>
        /// <returns>执行结果受影响行数</returns>
        public int UpdateStatus(int[] ids, int status)
        {
            //拼接要更新的ID
            string ps = string.Join(",", ids);
            string sql = @"UPDATE [&#WednTablePrefix%&_Users]
                                   SET
                                        [user_status]=@Status
                                    WHERE 
                                        [user_id] in (" + ps + ");";
            return SqlHelper.ExecuteNonQuery(sql, new SqlParameter("@Status", status));
        }

        /// <summary>
        /// 批量更新用户角色, 返回执行结果受影响行数
        /// </summary>
        /// <param name="ids">用户ID</param>
        /// <param name="role">更新的角色(0:管理员,1:撰写人,2:订阅者)</param>
        /// <returns>执行结果受影响行数</returns>
        public int UpdateRole(int[] ids, int role)
        {
            //拼接要更新的ID
            string ps = string.Join(",", ids);
            string sql = @"UPDATE [&#WednTablePrefix%&_Users]
                                   SET
                                        [user_role]=@Role
                                    WHERE 
                                        [user_id] in (" + ps + ");";
            return SqlHelper.ExecuteNonQuery(sql, new SqlParameter("@Role", role));
        }

        /// <summary>
        /// 获取表中主键最大值, 返回主键最大值
        /// </summary>
        /// <returns>主键最大值</returns>
        public int SelectMaxId()
        {
            return SqlHelper.SelectMaxId("user_id", "&#WednTablePrefix%&_Users");
        }

        /// <summary>
        /// 判断用户名是否存在,如果存在则返回TRUE,否则返回FALSE
        /// </summary>
        /// <param name="userName">要判断的用户名</param>
        /// <returns>存在则返回TRUE,否则返回FALSE</returns>
        public bool Exists(string userName)
        {
            return SqlHelper.ColumnExistsValue("&#WednTablePrefix%&_Users", "user_login", userName);
        }

        /// <summary>
        /// 选取用户总数
        /// </summary>
        /// <param name="where">选择条件(忽略则传入null或空值)(不能使用参数, 要使用参数化写重载)</param>
        /// <returns>总数</returns>
        public int SelectUsersCount(string where)
        {
            string sql = @"SELECT COUNT (1) FROM [&#WednTablePrefix%&_Users] WHERE {0};";
            sql = string.Format(sql, string.IsNullOrEmpty(where) ? string.Empty : where);
            return (int)SqlHelper.ExecuteScalar(sql);
        }

        /// <summary>
        /// 选取用户总数
        /// </summary>
        /// <param name="where">选择条件(忽略则传入null或空值)(不能使用参数, 要使用参数化写重载)</param>
        /// <returns>总数</returns>
        private int SelectUsersCount(string where, SqlParameter[] parmeters)
        {
            string sql = @"SELECT COUNT (1) FROM [&#WednTablePrefix%&_Users] WHERE {0};";
            sql = string.Format(sql, string.IsNullOrEmpty(where) ? string.Empty : where);
            return (int)SqlHelper.ExecuteScalar(sql, parmeters);
        }

        /// <summary>
        /// 选取用户信息表中所有用户信息, 返回对象集合, 如果不存在则返回null
        /// </summary>
        /// <returns>所有用户信息集合</returns>
        public List<User> SelectUserList()
        {
            //此处不宜使用"*"代替所有字段, 一个字慢
            string sql = @"SELECT
                                        [user_id],
                                        [user_login],
                                        [user_nickname],
                                        [user_password],
                                        [user_email],
                                        [user_url],
                                        [user_regdate],
                                        [user_status],
                                        [user_role]
                                    FROM
                                        [&#WednTablePrefix%&_Users];";
            return Select(sql);
        }

        /// <summary>
        /// 根据各种检索条件选取用户信息表中所有用户信息, 返回用户对象集合, 如果不存在则返回null
        /// </summary>
        /// <param name="keyword">关键字(忽略该条件则传入null)</param>
        /// <param name="status">状态(忽略该条件则传入-1)</param>
        /// <param name="role">角色(忽略该条件则传入-1)</param>
        /// <param name="pageSize">文章数目</param>
        /// <param name="pageIndex">页码</param>
        /// <param name="orderField">排序字段</param>
        /// <param name="order">排序方式(0:降序(desc)|1:升序(asc))</param>
        /// <param name="recordCount">返回查询到的记录数</param>
        /// <returns>所有满足条件的用户信息集合</returns>
        public List<User> SelectUserList(string keyword, int status, int role, int pageSize, int pageIndex, string orderField, int order, out int recordCount)
        {
            List<string> conditions = new List<string>();
            List<SqlParameter> ps = new List<SqlParameter>();
            if (!string.IsNullOrEmpty(keyword))
            {
                conditions.Add(string.Format(" [user_login] LIKE '%{0}%' OR [user_nickname] LIKE '%{0}%' ", keyword));
            }
            if (status != -1)
            {
                conditions.Add(" [user_status]=@Status ");
                ps.Add(new SqlParameter("@Status", status));
            }
            if (role != -1)
            {
                conditions.Add(" [user_role]=@Role ");
                ps.Add(new SqlParameter("@Role", role));
            }
            string where = conditions.Count > 0 ? string.Join(" AND ", conditions.ToArray()) : null;
            recordCount = SelectUsersCount(where, ps.ToArray());
            string col = "[user_id],[user_login],[user_nickname],[user_password],[user_email],[user_url],[user_regdate],[user_status],[user_role]";
            string sql = SqlHelper.MakePagedSql("&#WednTablePrefix%&_Users", col, pageSize, pageIndex, orderField, order, where);
            return Select(sql, ps.ToArray());
        }

        /// <summary>
        /// 根据用户ID获取用户信息表中用户信息对象, 返回用户信息对象, 如果不存在则返回null
        /// </summary>
        /// <param name="id">用户ID</param>
        /// <returns>用户信息对象, 不存在则返回NULL</returns>
        public User SelectUser(int id)
        {
            //此处不宜使用"*"代替所有字段, 一个字慢
            string sql = @"SELECT TOP 1
                                        [user_id],
                                        [user_login],
                                        [user_nickname],
                                        [user_password],
                                        [user_email],
                                        [user_url],
                                        [user_regdate],
                                        [user_status],
                                        [user_role]
                                    FROM
                                        [&#WednTablePrefix%&_Users]
                                    WHERE
                                        [user_id]=@Id;";
            List<User> list = Select(sql, new SqlParameter("@Id", id));
            return list != null ? list[0] : null;
        }

        /// <summary>
        /// 根据用户ID获取用户信息表中用户信息对象, 返回用户信息对象, 如果不存在则返回null
        /// </summary>
        /// <param name="login">用户登录名</param>
        /// <returns>用户信息对象, 不存在则返回NULL</returns>
        public User SelectUser(string login)
        {
            string sql = @"SELECT TOP 1
                                        [user_id],
                                        [user_login],
                                        [user_nickname],
                                        [user_password],
                                        [user_email],
                                        [user_url],
                                        [user_regdate],
                                        [user_status],
                                        [user_role]
                                    FROM
                                        [&#WednTablePrefix%&_Users]
                                    WHERE
                                        [user_login]=@Login;";
            List<User> list = Select(sql, new SqlParameter("@Login", login));
            return list != null ? list[0] : null;
        }

        /// <summary>
        /// 私有执行操作
        /// </summary>
        /// <param name="user">执行对象</param>
        /// <param name="sql">执行语句</param>
        /// <returns>返回执行结果受影响行数</returns>
        private static int ExecuteSql(User user, string sql)
        {
            return SqlHelper.ExecuteNonQuery(sql,
                new SqlParameter("@Login", user.Login.ToLower()),//用户登录名只允许是小写
                new SqlParameter("@NickName", user.NickName),
                new SqlParameter("@Password", user.Password),
                new SqlParameter("@Email", user.Email),
                new SqlParameter("@Url", user.Url),
                new SqlParameter("@RegDate", user.RegDate),
                //new SqlParameter("@Avatar", user.Avatar),
                new SqlParameter("@Status", user.Status),
                new SqlParameter("@Role", user.Role),
                new SqlParameter("@Id", user.Id)
                );
        }
        /// <summary>
        /// 私有选择方法, 如果不存在则返回null
        /// </summary>
        /// <param name="sql">选择语句</param>
        /// <param name="parameter">参数列表</param>
        /// <returns>数据集合</returns>
        private static List<User> Select(string sql, params SqlParameter[] parameter)
        {
            List<User> list = null;
            using (SqlDataReader reader = SqlHelper.ExecuteReader(sql, parameter))
            {
                if (reader.HasRows)
                {
                    list = new List<User>();
                    while (reader.Read())
                    {
                        User user = new User()
                        {
                            Id = reader.GetInt32(0),
                            Login = reader.GetString(1),
                            NickName = reader.GetString(2),
                            Password = reader.GetString(3),
                            Email = reader.IsDBNull(4) ? null : reader.GetString(4),
                            Url = reader.IsDBNull(5) ? null : reader.GetString(5),
                            RegDate = reader.GetDateTime(6),
                            Status = reader.GetInt32(7),
                            Role = reader.GetInt32(8)
                        };
                        list.Add(user);
                    }
                }
            }
            return list;
        }
    }
}
